﻿<%@ WebHandler Language="C#" Class="RbSpecialItemPlanDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
using System.Collections;

/**
 * Description: 热表专项计划看板
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.2.15
 **/

public class RbSpecialItemPlanDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "addItem":
                        r = addItem(jObject);//添加
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "updateItem":
                        r = updateItem(jObject);//更新
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "deleteItem":
                        r = deleteItem(jObject);//删除
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMonthData":
                        r = getMonthData(jObject);//查询本月
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWeekData":
                        r = getWeekData(jObject);//查询本周
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTodayData":
                        r = getTodayData(jObject);//查询今日
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "saveImportData":
                        r = saveImportData(jObject);//保存导入数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    case "getTeamInfo":
                        r = getTeamInfo(jObject);//查询班组所属工区
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //添加
    Result addItem(JObject jObject)
    {
        string sql = @"
INSERT INTO rbspeciallyitemplan(id,itemresource,itemcontent,itemfinishprogress,needfinishdate,finishdate,responseteam,createemployee,createdate,responseemployee,schedule)
VALUES('{0}','{1}','{2}','{3}',to_date('{4}','yyyy/mm/dd'),to_date('{5}','yyyy/mm/dd'),'{6}','{7}',sysdate,'{8}','{9}')";

        string ID = Guid.NewGuid().ToString();
        string itemresource = jObject["itemresource"].ToString();
        string itemcontent = jObject["itemcontent"].ToString();
        string itemfinishprogress = jObject["itemfinishprogress"].ToString();
        string needfinishdate = jObject["needfinishdate"].ToString();
        string finishdate = jObject["finishdate"].ToString();
        string responseteam = jObject["responseteam"].ToString();
        string createemployee = jObject["createemployee"].ToString();
        string responseemployee = jObject["responseemployee"].ToString();
        string schedule = jObject["schedule"].ToString();

        sql = string.Format(sql, ID, itemresource, itemcontent, itemfinishprogress, needfinishdate, finishdate, responseteam, createemployee, responseemployee, schedule);

        int count = dal.ExecuteSql(sql);

        Result r = new Result();
        r.data = null;
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //更新
    Result updateItem(JObject jObject)
    {
        string sql = @"
UPDATE rbspeciallyitemplan t
SET t.itemresource = '{0}',t.itemcontent='{1}',t.itemfinishprogress='{2}',t.needfinishdate=to_date('{3}','yyyy/mm/dd'),t.finishdate=to_date('{4}','yyyy/mm/dd'),
    t.responseteam='{5}',t.lastchangeemployee = '{6}',t.lastchangedate = sysdate,t.responseemployee = '{7}',t.schedule='{9}'
WHERE t.id = '{8}'";

        string id = jObject["id"].ToString();
        string itemresource = jObject["itemresource"].ToString();
        string itemcontent = jObject["itemcontent"].ToString();
        string itemfinishprogress = jObject["itemfinishprogress"].ToString();
        string needfinishdate = jObject["needfinishdate"].ToString().Split(' ')[0];
        string finishdate = jObject["finishdate"].ToString().Split(' ')[0];
        string responseteam = jObject["responseteam"].ToString();
        string lastchangeemployee = jObject["lastchangeemployee"].ToString();
        string responseemployee = jObject["responseemployee"].ToString();
        string schedule = jObject["schedule"].ToString();

        sql = string.Format(sql, itemresource, itemcontent, itemfinishprogress, needfinishdate, finishdate, responseteam, lastchangeemployee, responseemployee, id, schedule);

        int count = dal.ExecuteSql(sql);

        Result r = new Result();
        r.data = null;
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getSql(JObject jObject)
    {
        string sql = @"
SELECT t.id,t.itemresource,t.itemcontent,t.itemfinishprogress,t.needfinishdate,t.finishdate,t.responseteam,t.responseemployee,t.schedule,
       CASE WHEN t.finishdate IS NULL THEN '未完成'
            WHEN t.finishdate IS NOT NULL AND TRUNC(t.finishdate) > TRUNC(t.needfinishdate) THEN '超期完成'
            ELSE '按时完成' 
       END status
FROM rbspeciallyitemplan t
WHERE 1=1 ";
        //项目来源
        if (jObject["itemresource"] != null && jObject["itemresource"].ToString() != "")
        {
            sql += string.Format(" AND t.itemresource LIKE '%{0}%'", jObject["itemresource"].ToString());
        }
        //项目内容
        if (jObject["itemcontent"] != null && jObject["itemcontent"].ToString() != "")
        {
            sql += string.Format(" AND t.itemcontent LIKE '%{0}%'", jObject["itemcontent"].ToString());
        }
        //措施
        if (jObject["itemfinishprogress"] != null && jObject["itemfinishprogress"].ToString() != "")
        {
            sql += string.Format(" AND t.itemfinishprogress LIKE '%{0}%'", jObject["itemfinishprogress"].ToString());
        }
        //责任单位
        if (jObject["responseteam"] != null && jObject["responseteam"].ToString() != "")
        {
            sql += string.Format(" AND t.responseteam LIKE '%{0}%'", jObject["responseteam"].ToString());
        }
        //责任人
        if (jObject["responseemployee"] != null && jObject["responseemployee"].ToString() != "")
        {
            sql += string.Format(" AND t.responseemployee = '{0}'", jObject["responseemployee"].ToString());
        }

        //要求完成时间
        if (jObject["needfinishdate"] != null && jObject["needfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["needfinishdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND t.needfinishdate >= to_date('{0}','yyyy-MM-dd') ", arr[0].ToString());
                sql += string.Format(" AND t.needfinishdate <= to_date('{0}','yyyy-MM-dd') ", arr[1].ToString());
            }
        }
        //实际完成时间
        if (jObject["finishdate"] != null && jObject["finishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["finishdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND t.finishdate >= to_date('{0}','yyyy-MM-dd') ", arr[0].ToString());
                sql += string.Format(" AND t.finishdate <= to_date('{0}','yyyy-MM-dd') ", arr[1].ToString());
            }
        }
        //状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "未完成")
            {
                sql += " AND t.finishdate IS NULL";
            }
            else if (status == "超期完成")
            {
                sql += " AND t.finishdate IS NOT NULL AND TRUNC(t.finishdate) > TRUNC(t.needfinishdate)";
            }
            else
            {
                sql += " AND t.finishdate IS NOT NULL AND TRUNC(t.finishdate) <= TRUNC(t.needfinishdate)";
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //删除
    Result deleteItem(JObject jObject)
    {
        string sql = string.Format("DELETE rbspeciallyitemplan t WHERE t.id='{0}'", jObject["id"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getMonthData(JObject jObject)
    {
        string sql = @"
SELECT SUM(DECODE(t.finishdate,NULL,0,1)) finished,
       SUM(DECODE(t.finishdate,NULL,1,0)) notfinished
FROM rbspeciallyitemplan t";
        JArray arr = JArray.Parse(jObject["thismonth"].ToString());
        sql += string.Format(" WHERE t.needfinishdate >= to_date('{0}','YYYY/MM/DD') AND t.needfinishdate <= to_date('{1}','YYYY/MM/DD') ", arr[0].ToString(), arr[1].ToString());

        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getWeekData(JObject jObject)
    {
        string sql = @"
SELECT SUM(DECODE(t.finishdate,NULL,0,1)) finished,
       SUM(DECODE(t.finishdate,NULL,1,0)) notfinished
FROM rbspeciallyitemplan t";
        JArray arr = JArray.Parse(jObject["thisweek"].ToString());
        sql += string.Format(" WHERE t.needfinishdate >= to_date('{0}','YYYY/MM/DD') AND t.needfinishdate <= to_date('{1}','YYYY/MM/DD') ", arr[0].ToString(), arr[1].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getTodayData(JObject jObject)
    {
        string sql = @"
SELECT SUM(DECODE(t.finishdate,NULL,0,1)) finished,
       SUM(DECODE(t.finishdate,NULL,1,0)) notfinished
FROM rbspeciallyitemplan t";
        sql += string.Format(" WHERE t.needfinishdate = to_date('{0}','YYYY/MM/DD')", jObject["today"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result saveImportData(JObject jObject)
    {
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(jObject["willBeSavedData"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            string sql = "SELECT * FROM rbspeciallyitemplan t WHERE t.itemcontent = '{0}' AND t.responseteam = '{1}'";
            sql = string.Format(sql, dr["itemcontent"].ToString(), dr["responseteam"].ToString());
            DataTable dt = dal.GetDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                sql = @"
UPDATE rbspeciallyitemplan t 
SET t.itemresource = '{0}',t.itemfinishprogress = '{1}',
    t.needfinishdate = to_date('{2}','yyyy-mm-dd'),t.finishdate = to_date('{3}','yyyy-mm-dd'),t.responseemployee = '{4}',
    t.lastchangeemployee = '{5}',t.lastchangedate = sysdate,t.schedule = '{8}'
WHERE t.itemcontent = '{6}' AND t.responseteam = '{7}'";
                sql = string.Format(sql,
                    dr["itemresource"].ToString(),
                    dr["itemfinishprogress"].ToString(),
                    dr["needfinishdate"].ToString(),
                    dr["finishdate"].ToString(),
                    dr["responseemployee"].ToString(),
                    jObject["employeeid"].ToString(),
                    dr["itemcontent"].ToString(),
                    dr["responseteam"].ToString(),
                    dr["schedule"].ToString().Contains("%") ? dr["schedule"].ToString().TrimEnd('%') : dr["schedule"].ToString()
                    );
                sqlList.Add(sql);
            }
            else
            {
                sql = @"
INSERT INTO rbspeciallyitemplan(id,itemresource,itemcontent,itemfinishprogress,needfinishdate,finishdate,createemployee,createdate,responseteam,responseemployee,schedule)
VALUES('{0}','{1}','{2}','{3}',to_date('{4}','yyyy/mm/dd'),to_date('{5}','yyyy/mm/dd'),'{6}',sysdate,'{7}','{8}','{9}')";
                sql = string.Format(sql,
                    Guid.NewGuid().ToString(),//唯一id
                    dr["itemresource"].ToString(),//项目来源
                    dr["itemcontent"].ToString(),//项目内容
                    dr["itemfinishprogress"].ToString(),//措施
                    dr["needfinishdate"].ToString(),//要求完成时间
                    dr["finishdate"].ToString(),//实际完成时间
                    jObject["employeeid"].ToString(),//创建人
                    dr["responseteam"].ToString(),//责任单位
                    dr["responseemployee"].ToString(),//责任人
                    dr["schedule"].ToString().Contains("%") ? dr["schedule"].ToString().TrimEnd('%') : dr["schedule"].ToString()
                    );
                sqlList.Add(sql);
            }
        }

        Result r = new Result();
        dal.ExecuteSqlTran(sqlList);
        r.data = null;
        r.total = Convert.ToInt32(dtPostData.Rows.Count);
        r.meta = new Result.Meta(200, "保存成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns.Add("schedule2", typeof(string));
        foreach (DataRow dr in dtResult.Rows)
        {
            string schedule = dr["schedule"].ToString();
            if (schedule != "")
            {
                dr["schedule2"] = schedule + "%";
            }
        }

        dtResult.Columns.Remove("id");
        dtResult.Columns.Remove("schedule");

        dtResult.Columns["itemresource"].Caption = "项目来源";
        dtResult.Columns["itemcontent"].Caption = "项目内容";
        dtResult.Columns["itemfinishprogress"].Caption = "措施";
        dtResult.Columns["needfinishdate"].Caption = "要求完成时间";
        dtResult.Columns["finishdate"].Caption = "实际完成时间";
        dtResult.Columns["responseteam"].Caption = "责任单位";
        dtResult.Columns["responseemployee"].Caption = "责任人";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["schedule2"].Caption = "完成进度";

        ExportHelper.ExportExcel(dtResult, "热表专项计划看板导出数据");
    }

    Result getTeamInfo(JObject jObject)
    {
        string sql = @"
SELECT t.teamid,t.teamname,w.workcenterid,w.workcentername,f.factoryid,f.factoryname
FROM team t
LEFT JOIN Workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN factory f ON f.factoryid = w.factoryid
WHERE t.teamname = '{0}'";
        sql = string.Format(sql, jObject["teamname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}